IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[FunStringSplit]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	DROP FUNCTION dbo.[FunStringSplit]
GO

-- =============================================
-- Author:        Jimmy 
-- Description:  Split string to a table.
-- Created        07/09/20008
-- =============================================
--XiaoQ		5/21/2009	split to varchar(max)
--Sukit		11/03/2009	CO2150:Fix bug in while.

CREATE FUNCTION dbo.FunStringSplit
(
    @Str NVARCHAR(MAX),
    @SplitBy NVARCHAR(100)
)
RETURNS @ReturnTable Table(Id int IDENTITY(1,1),Item NVARCHAR(max))
AS
BEGIN
	IF RIGHT(@Str,Len(@SplitBy))!=@SplitBy SET @Str=@Str+@SplitBy
	DECLARE @beginLocation int, @endLocation INT
	SELECT @beginLocation=1,@endLocation = CHARINDEX(@SplitBy,@Str)
	WHILE @endLocation>0
	BEGIN
		INSERT INTO @ReturnTable SELECT RTRIM(LTRIM(SUBSTRING(@Str,@beginLocation,@endLocation-@beginLocation)))
		SELECT @beginLocation=@endLocation+len(@SplitBy),@endLocation=charIndex(@SplitBy,@Str,@endLocation+len(@SplitBy))
	END
	RETURN 

END
GO
